import yaml
import urllib
import urllib2
import sqlite3
import csv
import datetime
import calendar
import httplib
import urlparse
import re
import threading
from threading import Thread
from time import gmtime,strftime,time,sleep,ctime
from BeautifulSoup import BeautifulSoup, SoupStrainer
from Queue import Queue

class StockRobot:
    """
    A robot get the lastest stock information which required in the config file.
    """
    config = yaml.load(open('config.yaml'))
    conn = sqlite3.connect(config['development']['database'])
    cursor = conn.cursor()
    symbol = "IBM"
    unCrawlUrls = None
    queue = Queue()
    thread = threading.Thread()
    
    def __init__(self):
        self.cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS schema_migrations(version varchar(16) UNIQUE)
            """
            )
        self.cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS stocks_list(
            symbol varchar(25) UNIQUE,
            name varchar(30),            
            exchange varchar(10)
            )
            """
            )
        self.cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS crawl_urls(
            symbol varchar (10),
            url varchar(255),
            crawled boolean
            )
            """
            )
        self.cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS proxys_list(
            address varchar(25) UNIQUE,
            type varchar(10)
            )
            """
            )
        self.cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS stocks_bank(
            symbol varchar(25),
            trade_date datetime,
            opening_price float,
            closeing_price float,            
            days_low float,
            days_high float,
            volume int,
            _52_week_low float,
            _52_week_high float,
            annualized_gain float,
            market_cap float,
            PEG_ratio float,
            dividend_yield float
            )          
            """            
        )
        self.conn.commit()
        self.__getLatestDbVersion__()
        self.__updateStockList__()
        self.__updateCrawlUrls__()
        
    def __updateStockList__(self):
        """
        Check stock's records is empty or not
        """
        self.cursor.execute(            
        """
        SELECT count(*) FROM stocks_list
        """        
        )
        recNum = self.cursor.fetchone()[0]
        if 0 == recNum:
            for exchange in self.config['exchange']:
                if 'ss' == exchange:
                    url = self.config['exchange']['ss']
                    re_link = "quote.eastmoney.com/sh"
                elif 'sz' == exchange:
                    url = self.config['exchange']['sz']
                    re_link = "quote.eastmoney.com/sz"
                else:
                    continue
                self.__getStockList__(exchange,url,re_link)

            self.conn.commit()
        else:
            # TODO
            pass
                
       
    def __getProxyList__(self):
        pass
    
    def __openProxy__(self,address):
        proxy_support = urllib2.ProxyHandler({'http':address})
        opener = urllib2.build_opener(proxy_support, urllib2.HTTPHandler)
        urllib2.install_opener(opener)
        return True
    
    def __updateCrawlUrls__(self):
        self.cursor.execute(     
        """
        SELECT * FROM stocks_list
        """
        )
        symbols = self.cursor.fetchall()
        for row in symbols:
            self.__setSymbol__(row[0])
            self.cursor.execute(  
            """
            INSERT INTO crawl_urls(symbol,url,crawled) VALUES (?,?,?)
            """            
            ,(self.symbol,self.__getYahooFinaceAPI__(),'0')
            )
        self.conn.commit()
        print "Crawl urls have been updated."
        return True
        
    def __getStockList__(self,exchange,url,re_link):
        url_is_exists = self.__httpExists__(url)
        if url_is_exists:            
            page = urllib2.urlopen(url).read()
            a_tag = SoupStrainer('a',href=re.compile(re_link))
            a_tags = [tag for tag in BeautifulSoup(page, parseOnlyThese=a_tag)]
            for line in a_tags:
                stock_info = str(line)[67:]
                name = stock_info[:-12]
                symbol = stock_info[-11:-5]+"." + exchange 
                stock_list = [symbol,name.decode('utf-8'),exchange]
                self.cursor.execute(  
                """
                INSERT INTO stocks_list(symbol,name,exchange) VALUES (?,?,?)
                """
                ,stock_list
                )
            print "Stock lists have been updated."
            return True                  
        else:
            return False        
        
    def __getLatestDbVersion__(self):        
        recNumInDb = self.__getRecNum__('schema_migrations')    
        if 0 == recNumInDb:
            self.__updateDbVersion__()
        else:
            self.cursor.execute(
                """
                SELECT * FROM schema_migrations ORDER BY version DESC
                """
                )
            latestDbVersion = self.cursor.fetchone()[0]
        return latestDbVersion
    
    def __updateDbVersion__(self):
        current_time = strftime("%Y%m%d%H%M%S",gmtime(time()+28800))
        self.cursor.execute(     
            """
            INSERT INTO schema_migrations (version) VALUES (%s)
            """
            %(current_time)
            )
        self.conn.commit()
    
    def __getRecNum__(self,tableName):
        self.cursor.execute(            
            """
            SELECT count(*) FROM %s
            """
            %(tableName)
        )
        recNum = self.cursor.fetchone()[0]
        return recNum
    
    def __setSymbol__(self,symbol):
        self.symbol = str(symbol)
    
    def __getStartTime__(self):       
        """
        Check stock's records is empty or not
        """
        self.cursor.execute(            
        """
        SELECT count(*) FROM stocks_bank WHERE symbol = "%s"
        """
        %(self.symbol)
        )
        recNum = self.cursor.fetchone()[0]      

        if 0 == recNum:
            start_time = "20000101"
        else:
            """
            Get latest trade time
            """
            self.cursor.execute(            
            """
            SELECT trade_date FROM stocks_bank WHERE symbol = "%s" ORDER BY trade_date DESC
            """
            %(self.symbol)
            )
            latest_trade_time = self.cursor.fetchone()[0]
            start_time = latest_trade_time[0:4]+latest_trade_time[5:7]+latest_trade_time[8:10]
        return start_time
    
    def __getEndTime__(self):        
        """
        Get last Friday
        """
        lastFriday = datetime.date.today()
        oneday = datetime.timedelta(days=1)
        while lastFriday.weekday()!= calendar.FRIDAY:
            lastFriday -= oneday
        return lastFriday.strftime("%Y%m%d")
        
    def __isLatestUpdate__(self):
        start_time = self.__getStartTime__()
        end_time = self.__getEndTime__()
        
        if start_time < end_time:
            isLatestUpdate = False
        elif start_time == end_time:
            isLatestUpdate = True
        else:
            isLatestUpdate = True        
        return isLatestUpdate
        
        
    def __getYahooFinaceAPI__(self):
        """
        Start time
        """
        start_month = str(self.__getStartTime__()[4:6])
        start_day = str(self.__getStartTime__()[6:8])
        start_year = str(self.__getStartTime__()[0:4])
        """
        End time
        """
        end_month = str(self.__getEndTime__()[4:6])
        end_day = str(self.__getEndTime__()[6:8])
        end_year = str(self.__getEndTime__()[0:4])
        """
        Get yahoo finace api
        """
        yahoo_finace_api = "http://ichart.yahoo.com/table.csv?s=" + self.symbol + "&a=" + start_month + "&b=" + start_day +"&c=" + start_year + "&d="+ end_month +"&e="+ end_day +"&f="+ end_year +"&g=d&ignore=.csv"
        #yahoo_finace_api = "http://table.finace.yahoo.com/table.csv?s=" + self.symbol
        return yahoo_finace_api      

    def __httpExists__(self,url):
        host,path = urlparse.urlsplit(url)[1:3]
        if ':' in host:
            host,port =host.split(':',1)
            try:
                port = int(port)
            except ValueError:
                print 'Invalid port number %r' % (port,)
                return False
        else:
            port = None
        
        try:
            connection = httplib.HTTPConnection(host,port=port)
            connection.request("HEAD",path)
            resp = connection.getresponse()
            if resp.status == 200 or 301 or 400:
                found = True
            elif resp.status == 302 or 404:
                found = self.__httpExists__(urlparse.urljoin(url,resp.getheader('location','')))
            else:
                print "Status %d %s: %s" % (resp.status,resp.reason,url)
                found = False
        except Exception,e:
            print e.__class__,e,url
            found = False
        return found
    
    def __markCrawledUrl__(self,url):
        self.cursor.execute(                                                    
        """
        UPDATE crawl_urls SET crawled = '1' WHERE url ='%s'
        """
        %(url)
        )
    
    def __getFinaceHistory__(self,url):
        url = str(url)
        url_is_exists = self.__httpExists__(url)        
        if url_is_exists:
            request = urllib2.Request(url)
            request.add_header('User-Agent', 'Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.8.1.14) Gecko/20080404 (FoxPlus) Firefox/2.0.0.14')
            try:
                yahoo_csv = urllib2.urlopen(request)
                history = csv.reader(yahoo_csv)
                for row in history:
                    if ['Date', 'Open', 'High', 'Low', 'Close', 'Volume', 'Adj Close'] != row:
                        symbol_history = [self.symbol,row[0],row[1],row[2],row[3],row[4],row[5]]
                        self.cursor.execute(     
                        """
                        INSERT INTO stocks_bank(symbol,trade_date,opening_price,days_high,days_low,closeing_price,volume) VALUES (?,?,?,?,?,?,?)
                        """
                        ,symbol_history
                        )             

                    else:
                        pass                
                self.__markCrawledUrl__(url)
                print "Information of " + self.symbol + " has been crawled."
                
            except Exception,e:
               print "Information of "  + self.symbol + " can not been got."
               print e         
           
        else:
            print " Information of " + self.symbol + " is not exist." 
    
    def __getUncrawlUrls__(self):
        self.cursor.execute(     
        """
        SELECT COUNT(*) FROM crawl_urls
        """
        )
        recNum = self.cursor.fetchone()[0]
        if 0 == recNum:
            return False
        else:
            self.cursor.execute(     
            """
            SELECT symbol,url FROM crawl_urls WHERE crawled = '0'
            """
            )
            self.unCrawlUrls = self.cursor.fetchall()
            return True
        
    def run(self,symbol,url):
        self.__setSymbol__(symbol)
        if False == self.__isLatestUpdate__():                
            self.__getFinaceHistory__(url)
        else:
            print "Information of " + symbol + " is the latest."
    
    def dig(self,delay_time=30):
        urlCounter = 0
        if self.__getUncrawlUrls__():
            for row in self.unCrawlUrls:
                self.__setSymbol__(row[0])
                if False == self.__isLatestUpdate__():                
                    self.__getFinaceHistory__(row[1])
                    sleep(delay_time)
                else:
                    print "Information of " + row[0] + " is the latest."
                    continue
                urlCounter =+1
                while 0 == urlCounter%20:
                    self.conn.commit()
        else:
            print "Stocks' information is the latest."

    def writeUrls(self):        
        self.__getUncrawlUrls__()
        for row in self.unCrawlUrls:
            self.queue.put(row)            
        
    def crawl(self,spiders = 10):        
        self.writeUrls()        
        threads = []
        for index in xrange(0, spiders):
            args = self.queue.get()
            spider = threading.Thread(target=self.run,args=(args[0],args[1]))
            threads.append(spider)
        
        for spider in threads:
            spider.start()
            
        for spider in threads:
            spider.join()
            
        self.__updateDbVersion__()
        self.conn.commit()